﻿# -*- coding: utf-8 -*-
# 
u"""
Modulo utilizado no primeiro projecto da Disciplina de Linguagem de Programação
que consiste em elaboração de uma aplicação capaz de fazer leituras dos dados
num ficheiro excel(.xls) e a sua respectiva escrita dnum ficheiro de base de
dados no formato sqlite.
Utilizará os dados da base de daos para gerar quer lista deresultados
quer gráficos estatísticos representativos
de:
- Total de Colocados por Escola
- Total de Colocados por Distrito
- Total de Vagas Sobrantes por Escola
- Total de Vagas Sobrantes por Distrito
- Percentagem de Colocados por Escola
- Permilagem de Colocados por Distrito
"""
# modulo de operações de leitura e escrita sobre um ficheiro
# de base de dados sqlite3
# 
# IPBeja - Escola Superior de Tecnologia e Getão
# Engenharia Informática
# Elísio Gonçalves Ribeiro nº4708
# Em Outubro-2013


import sqlite3
import re

#
#Modulo utilizado no primeiro projecto da Disciplina de Linguagem de Programação
#que consiste em elaboração de uma aplicação capaz de fazer leituras dos dados
#num ficheiro excel (.xls) e a sua respectiva escrita dnum ficheiro de base de
#dados no formato sqlite.
#
##
##############################################################################
##

# metodo que cria uma ligaçao a um ficheiro sqlite no disco
# cria novo ficheiro no endereco indicado se não existir
#
def connectingTo_db(db_path):
    '''
    create a sqlite db connection to a file on the disk
    create new file with given name if it doesnt exist
    and returns the connection to the db
    bd_path - name or path to the db file on the disk
    '''
    try:
        # bd_path = endereco_bd.encode("utf8")        
        # conexao/criacao no disco da Base de Dados
        db_conn = sqlite3.connect(db_path)
    except:
        raise
    else:
        return db_conn
    pass
pass

##
##############################################################################
# criação de tabelas apartir dos nomes das folhas+
# e dos valores das colunas
#
def creating_tables(cursor, table_name, col_names, f_keys=None):
    '''
    create tables based on given parameters
    cursor     - cursor of a connected db
    table_name - table name to create in the db
    col_names  - list of tuples containing (<field>, <type>)
    '''
    pk = col_names[0]
    sql_cmd = 'CREATE TABLE IF NOT EXISTS {0} \
    ({1} {2} PRIMARY KEY NOT NULL,'.format(table_name, pk[0], pk[1])
    for field in col_names[1:]:
        sql_cmd += field[0] +' '+ field[1] + ','                              #adding fields to the sql_stment 
    pass
    if (f_keys != None):                                                      #checks foreign keys
        fkcmd = ""
        for fk in f_keys:  
            sql_cmd += " fk_{0} {1}, ".format(fk[1][0],fk[1][1])   
            fkcmd += "FOREIGN KEY (fk_{0}) REFERENCES {1}({2}),".format(fk[1][0], fk[0], fk[1][0])
        pass
        sql_cmd += fkcmd                                                      #adding foreign keys constrains
    pass
    sql_cmd = sql_cmd[:sql_cmd.rfind(',')]
    sql_cmd += ')'                                                            #finalize the sql_stment
    try:
        cursor.execute(sql_cmd)                                               #actually creating the table
    except: pass
pass

##
###############################################################################
#recebe um cursor com ligaçao a db lista de dados a preencher na 
#tabela e a respectiva tabela e faz o preenchimento
#
def filling_dbTable(cursor, data_lst, table_name):
    '''
    uses table_name and colums_names to create sql_insert_statement
    then uses data_lst to fill the given table name
    cursor      - cursor of a connected db
    data_lst    - list of data to load on the db table
    table_name  - table name
    '''
    try:
        each_field = '?,'*len(data_lst[0])
        sql_insert = 'INSERT INTO {0} VALUES({1})'.format(table_name, each_field[:-1])
        res = cursor.executemany(sql_insert, data_lst)                                   #execute the above sql_insert_stments
    except: 
        raise
    pass
pass

##
##############################################################################
#metodo para confirmar a escrita dos 
#dadosno disco e fechar a ligaçãoo à base de dados
#
def turnOff_conn(bd_con, cursor):
    '''
    confirms the saving of all data on the disk
    and then do the close on all connections to the db
    db_conn  - a connection object of current db
    cursor   - the db cursor
    '''
    try:
        cursor.close()
        bd_con.commit()
        bd_con.close()
    except:
        return
    pass
pass

##
###############################################################################
#recebe o nome de uma base de dados, faz a conecção e executa uma pesquisa
#pelo numero de vagas sobrantes para cada escola e retorna a pesquisa
def getFPlaces_PerSchool(dbname):
    '''
    queries the database and returns a list of places that 
    have not been ocupied by any student. 
    the list contains free places for each school
    dbname  - dbase name where to query/retrieve the list from
    '''
    
    sqlstmt = """
    SELECT  código_instituição, nome_instituição, SUM(vagas_sobrantes) 'Total_Vagas' 
    FROM  escolas, concurso
    WHERE código_instituição==fk_código_instituição
    GROUP BY nome_instituição
    ORDER BY Total_Vagas DESC
    """
    
    return __querying_dbase(dbname, sqlstmt)
pass

##
###############################################################################
#recebe o nome de uma base de dados, faz a conecção e executa uma pesquisa
#pelo numero de vagas sobrantes para cada distrito e retorna a pesquisa
#
def getFPlaces_PerState(dbname):
    '''
    queries the database and returns a list of places that 
    have not been ocupied by any student. 
    the list contains the sums of free places for each state
    dbname  - dbase name where to query/retrieve the list from
    '''
    sqlstmt = """
    SELECT  código_instituição, cidade, SUM(vagas_sobrantes) 'Total_Vagas' 
    FROM  escolas, concurso
    WHERE código_instituição==fk_código_instituição
    GROUP BY cidade
    ORDER BY Total_Vagas DESC
    """
    
    return __querying_dbase(dbname, sqlstmt)
pass

##
###############################################################################
#recebe o nome de uma base de dados, faz a conecção e executa uma pesquisa
#pelo numero de colocados para cada distrito e retorna a pesquisa
#
def getPlaced_PerState(dbname):
    '''
    queries the database and returns a list of 
    total placed students for each state
    dbname  - dbase name where to query/retrieve the list from
    '''
    sqlstmt = """
    SELECT  código_instituição, cidade, SUM(colocados) 'Total_Colocados' 
    FROM  escolas, concurso
    WHERE código_instituição==fk_código_instituição
    GROUP BY cidade
    ORDER BY Total_Colocados DESC
    """
    
    return __querying_dbase(dbname, sqlstmt)
pass

##
###############################################################################
#recebe o nome de uma base de dados, faz a conecção e executa uma pesquisa
#pelo numero de colocados para cada escola e retorna a pesquisa
#
def getPlaced_PerSchool(dbname):
    '''
    queries the database and returns a list of 
    total placed students for each school
    dbname  - dbase name where to query/retrieve the list from
    '''
    sqlstmt = """
    SELECT  código_instituição, nome_instituição, SUM(colocados) 'Total_Colocados' 
    FROM  escolas, concurso
    WHERE código_instituição==fk_código_instituição 
    GROUP BY nome_instituição
    ORDER BY Total_Colocados DESC
    """
    
    return __querying_dbase(dbname, sqlstmt)
pass

##
###############################################################################
#recebe o nome de uma base de dados, faz a conecção e executa uma 
#pesquisa e calcula a percentagem de colocados em cada escola em 
#relação ao total de colocados e retorna a pesquisa
#
def getPerc_Placed(dbname):
    '''
    queries the database and returns a list of 
    percentual value of placed students per school
    dbname  - dbase name where to query/retrieve the list from
    '''
    sqlstmt = """
    SELECT código_instituição, nome_instituição, SUM(colocados) AS 'Total_Colocados',
    ((SUM(colocados) *100.0)/(SELECT SUM(colocados) FROM concurso))  AS 'Percentagem'
    FROM concurso, escolas
    WHERE fk_código_instituição==código_instituição
    GROUP BY nome_instituição
    ORDER BY Percentagem DESC
    """
    
    return __querying_dbase(dbname, sqlstmt)
pass

#retorna a lista dos dados para pesquisa por escolas
def get_school_choices(dbname):
    '''
    queries the database and returns a list of 
    value of total initial available places, filled places, and remainder
    dbname  - dbase name where to query/retrieve the list from
    '''    
    sqlstmt = """
    SELECT código_instituição, nome_instituição
    FROM escolas
    ORDER BY código_instituição DESC
    """
    
    return __querying_dbase(dbname, sqlstmt)
pass

#retornar lista dos dados para pesquisa por curso
def get_course_choices(dbname):
    """
    """
    sqlstmt = """
    SELECT código_instituição, nome_instituição
    FROM escolas
    ORDER BY código_instituição DESC
    JOIN(SELECT código_curso, nome_curso
    FROM cursos
    ORDER BY código_curso DESC)
    """
    return __querying_dbase(dbname, sqlstmt)
pass

#
def get_all_places(dbname):
    """
    """
    sqlstmt = """
    SELECT fk_código_instituição, nome_instituição, 
    fk_código_curso, nome_curso, vagas_iniciais, colocados, vagas_sobrantes
    FROM concurso, escolas, cursos
    WHERE fk_código_instituição==código_instituição
    AND código_curso==fk_código_curso
    ORDER BY fk_código_curso DESC
    """
    return __querying_dbase(dbname, sqlstmt)
pass

#
def get_tfp_PerSchool(dbname):
    '''
    queries the database and returns a list of 
    value of total initial available places per school
    dbname  - dbase name where to query/retrieve the list from
    '''
    sqlstmt = """
    SELECT código_instituição, SUM(vagas_iniciais) AS 'Total_Inicial'
    FROM concurso, escolas
    WHERE fk_código_instituição==código_instituição
    GROUP BY código_instituição
    ORDER BY Total_Inicial DESC
    """
    
    return __querying_dbase(dbname, sqlstmt)
pass

#retornar o total de vagas iniciais por distrito
def get_tfp_PerState(dbname):
    '''
    queries the database and returns a list of 
    value of initial available place per state
    dbname  - dbase name where to query/retrieve the list from
    '''
    sqlstmt = """
    SELECT cidade, SUM(vagas_iniciais) AS 'Total_Inicial'
    FROM concurso, escolas
    WHERE concurso.fk_código_instituição==escolas.código_instituição
    GROUP BY cidade
    ORDER BY Total_Inicial DESC
    """
    
    return __querying_dbase(dbname, sqlstmt)
pass

##
###############################################################################
#recebe o nome de uma base de dados, faz a conecção e executa uma 
#pesquisa e calcula a permilagem de colocados em cada distrito
#em relação ao total de colocados e retorna a pesquisa
#
def getPermil_Placed(dbname):
    '''
    queries the database and returns a list of 
    percentual value of placed students per state
    dbname  - dbase name where to query/retrieve the list from
    '''
    sqlstmt = """
    SELECT código_instituição, cidade, SUM(colocados) AS 'Total_Colocados',
    ((SUM(colocados) *1000.0)/(SELECT SUM(colocados) FROM concurso))  AS 'Permilagem'
    FROM concurso, escolas
    WHERE fk_código_instituição==código_instituição
    GROUP BY cidade
    ORDER BY Permilagem DESC
    """
    
    return __querying_dbase(dbname, sqlstmt)
pass

##
###############################################################################
#recebe o nome de uma base de dados, e a expressao do query a executar
#
def __querying_dbase(dbname, sqlstmt):
    '''
    queries the database and returns a list of results 
    acording to the sql statement places that 
    have not been ocupied by any student.
    dbname  - dbase name where to query/retrieve the list from
    sqlstmt - SELECT sql statement to be executed over the db
    '''
    res_lst = []    
    try:
        dbc = connectingTo_db(dbname)
        cursor = dbc.cursor()
        cursor = cursor.execute(sqlstmt)        
        res_lst = cursor.fetchall()
        t_columns = tuple([value[0] for value in cursor.description])
    except:
        turnOff_conn(dbc, cursor)
        raise
    else:
        turnOff_conn(dbc, cursor)
        return (t_columns, res_lst)
    pass
pass
